﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Reflection;
using System.Text;

namespace U8
{
    public class SqlHelper
    {

        public static DataTable Query(string cmdText, string strConn)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter dap = new SqlDataAdapter(cmdText, strConn);
            dap.Fill(dt);
            return dt;
        }

        public static int Execute(string cmdText, SqlConnection con, SqlTransaction tran)
        {
            return Execute(cmdText, null, con, tran);
        }
        public static int Execute(string cmdText, SqlParameter[] para, SqlConnection con, SqlTransaction tran)
        {
            int irow = 0;
            SqlCommand cmd = new SqlCommand(cmdText, con, tran);
            if (para != null)
                cmd.Parameters.AddRange(para);
            irow = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return irow;
        }

        public static int Execute(string cmdText, string strConn)
        {
            int irow = 0;
            using (SqlConnection con = new SqlConnection(strConn))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(cmdText, con);
                irow = cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            return irow;
        }

        public static List<TResult> ToList<TResult>(DataTable dt) where TResult : class, new()
        {
            //创建一个属性的列表
            List<PropertyInfo> prlist = new List<PropertyInfo>();
            //获取TResult的类型实例  反射的入口
            Type t = typeof(TResult);
            //获得TResult 的所有的Public 属性 并找出TResult属性和DataTable的列名称相同的属性(PropertyInfo) 并加入到属性列表 
            Array.ForEach<PropertyInfo>(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); });
            //创建返回的集合
            List<TResult> oblist = new List<TResult>();
            string tempName = "";
            foreach (DataRow row in dt.Rows)
            {
                //创建TResult的实例
                TResult ob = new TResult();
                //找到对应的数据  并赋值 
                foreach (PropertyInfo pi in prlist)
                {
                    tempName = pi.Name;
                    if (dt.Columns.Contains(tempName))
                    {
                        // Console.WriteLine(tempName);
                        //判断此属性是否有set
                        if (!pi.CanWrite)
                            continue;
                        object value = row[tempName];
                        if (value != DBNull.Value)
                        {
                            Type vtype = value.GetType();
                            if (pi.PropertyType.Name.ToLower() == "guid")
                                pi.SetValue(ob, string.IsNullOrEmpty(value.ToString()) ? null : Convert.ChangeType(value, pi.PropertyType), null);
                            else if (vtype.Name.ToLower() == "guid")
                                pi.SetValue(ob, value.ToString(), null);
                            else if (IsType(pi.PropertyType, "System.Nullable`1[System.DateTime]"))
                            {
                                try
                                {
                                    pi.SetValue(ob, (DateTime?)DateTime.ParseExact(value.ToString(), "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), null);
                                }
                                catch
                                {
                                    pi.SetValue(ob, (DateTime?)DateTime.ParseExact(value.ToString(), "yyyy-MM-dd", CultureInfo.InvariantCulture), null);
                                }
                            }
                            else
                            {

                                if (pi.PropertyType.Name.ToLower() == "boolean")
                                {
                                    bool bval = false;
                                    if (value.ToString() == "1" || value.ToString().ToLower() == "true")
                                        bval = true;

                                    pi.SetValue(ob, bval, null);
                                }
                                else
                                    pi.SetValue(ob, string.IsNullOrEmpty(value.ToString()) ? null : Convert.ChangeType(value, pi.PropertyType), null);
                            }

                        }
                        else
                        {
                            if (pi.PropertyType.ToString().ToLower().Contains("string"))
                                pi.SetValue(ob, "", null);
                        }
                        //pi.SetValue(ob, value, null);
                    }
                }
                //放入到返回的集合中.
                oblist.Add(ob);
            }
            return oblist;
        }
        public static bool IsType(Type type, string typeName)
        {
            if (type.ToString() == typeName)
                return true;
            if (type.ToString() == "System.Object")
                return false;

            return IsType(type.BaseType, typeName);
        }

        public static List<T> QueryList<T>(string sql, string strConn) where T : class, new()
        {
            DataTable dt = Query(sql, strConn);
            return ToList<T>(dt);
        }

        public static void SaveTable(DataTable dtData, SqlConnection Conn, SqlTransaction Tran, string[] noField, string DBName = "")
        {
            foreach (DataRow dr in dtData.Rows)
            {
                string sql = "Insert into  {0} ( {1} ) values ( {2} )";
                List<SqlParameter> paras = new List<SqlParameter>();
                List<string> fld = new List<string>();
                List<string> spFields = "ufts,pubufts".Split(',').ToList();
                if (noField != null)
                    spFields.AddRange(noField);
                foreach (DataColumn dc in dtData.Columns)
                {
                    string colName = dc.ColumnName;
                    if (spFields.Contains(colName.ToLower()))
                        continue;

                    fld.Add(colName);
                    paras.Add(new SqlParameter("@" + colName, dr[colName]));
                }
                sql = string.Format(sql, DBName + ".." + dtData.TableName, string.Join(",", fld.ToArray()), "@" + string.Join(",@", fld.ToArray()));
                Execute(sql, paras.ToArray(), Conn, Tran);
            }
        }
    }
}
